Contingency Table

Contingency Table in Excel

Contingency tables are notoriously labor-intensive to produce and involve computing the expected frequency for each cell. 

Many popular programs have the capability to make contingency tables, including Microsoft Excel (note that even in Excel, the process is quite complicated, involving the creation of pivot tables). 


A contingency table in Excel is created in Excel with the Pivot Table tool. 

A contingency table, sometimes called a two-way frequency table
is a tabular mechanism with at least two rows and two columns used in statistics to present categorical data in terms of frequency counts. 
More precisely, an r×c contingency table shows the observed frequency of two variables, the observed frequencies of which are arranged into r rows and c columns. 
The intersection of a row and a column of a contingency table is called a cell.

Contingency tables (also called crosstabs or two-way tables) are used in statistics to summarize the relationship between several categorical variables



gender
cup
cone
sundae
sandwich
other
male
592
300
204
24
80
female
410
335
180
20
55


For example, the above contingency table has two rows and five columns (not counting header rows/columns) and shows the results of a random sample of 2200 adults classified by two variables, namely gender and favorite way to eat ice cream (Larson and Farber 2014). 
One benefit of having data presented in a contingency table is that it allows one to more easily perform basic probability calculations, a feat made easier still by augmenting a summary row and column to the table.


gender
cup
cone
sundae
sandwich
other
total
male
592
300
204
24
80
1200
female
410
335
180
20
55
1000
total
1002
635
384
44
135
2200


The above table is an extended version of the first table obtained by adding a summary row and column. 
These summaries allow easier computation of several different probability-related quantities. 
For example, there's a 1002/2200 approx 45.54% probability that the person sampled prefers their ice cream in a cup, while the probability that a random participant is female is 1000/2200 approx 45.45%. 
What's more, computing conditional probabilities is made easier using contingency tables, e.g., the probability that a person prefers ice cream sandwiches given that the person is male is 24/1200=2%, while the conditional probability that a person is male given that ice cream sandwiches are preferred is 24/44 approx 54.54%.